#! /bin/sh

sql iidbdb << SQL_END

declare global temporary table stuff(
	string    varchar(50) not null not default,
	expected_nysiis_o varchar(50) not null not default,
	expected_nysiis varchar(50) not null not default
	) on commit preserve rows with norecovery;

\p\g

insert into stuff values ('john',           'JAN',      'JAN');
insert into stuff values ('john smith',     'JANSNAT', 'JANSNATH');
/* Test suffix removal for sr., jr and roman numerals */
insert into stuff values ('john smith sr.', 'JANSNAT', 'JANSNATH');
insert into stuff values ('john smith Jr.', 'JANSNAT', 'JANSNATH');
insert into stuff values ('john smith iii', 'JANSNAT', 'JANSNATH');
insert into stuff values ('john smith ivg', 'JANSNATAVG', 'JANSNATAVG');
insert into stuff values ('john smith IV',  'JANSNAT', 'JANSNATH');

/* Step 2 */
insert into stuff values ('stress',         'STRAS',  'STR');
insert into stuff values ('buzz',           'BAS',     'B');

/* Step 3 */
insert into stuff values ('Pfizzer',        'FFASAR',  'FASAR');
insert into stuff values ('Mackenzie',      'MCANSY',  'MCANSY');

/* Step 4 */
insert into stuff values ('six',            'SAX', 'SAC');
insert into stuff values ('sixteen',        'SAXTAN', 'SAXTAN');
insert into stuff values ('sex',            'SAX', 'SAC');
insert into stuff values ('yippee',         'YAPY', 'YAPY');
insert into stuff values ('hardie',         'HARDY', 'HARDY');
insert into stuff values ('GILMOURDT',      'GALNARD', 'GALNAD');

/* Step 5 */
insert into stuff values ('Severus septimus', 'SAFARASAPTAN', 'SAFARASAPTAN');

/* Step 7 */
insert into stuff values ('betwix',         'BATWAX', 'BATWAC');
insert into stuff values ('ear wax',        'EARWAX', 'ERWAX');
insert into stuff values ('wowza',          'WAS', 'WAS');

/* Step 8 */
insert into stuff values ('Oodnadatta',     'OADNADAT', 'ODNADAT');
insert into stuff values ('Kalgoorlie',     'CALGARLY', 'CALGARLY');

/* Step 9 */
insert into stuff values ('bought',         'BAGT', 'BAGT');

/* Step 10 */
insert into stuff values ('dodge',          'DADG', 'DAG');

/* Step 11 */
insert into stuff values ('staphlococus',   'STAFLACAC', 'STAFLACAC');

/* Step 12 */
insert into stuff values ('harden',         'HARDAN', 'HARDAN');
insert into stuff values ('THORIUM',        'THARAN', 'TARAN');
insert into stuff values ('other',          'OTAR', 'OTAR');
insert into stuff values ('mhrih',          'MHRAH', 'MHR');
insert into stuff values ('hotel',          'HATAL', 'HATAL');
insert into stuff values ('hilton hotel',   'HALTANATAL', 'HALTANATAL');
insert into stuff values ('school',         'SSAL', 'SCAL');
insert into stuff values ('sharp',          'SHARP', 'SARP');
insert into stuff values ('sith',           'SAT',   'SATH');

/* Step 13 */
insert into stuff values ('Knightsbridge',  'NNAGTSBRADG', 'NAGTSBRAG');
insert into stuff values ('Aardvaark',      'AARDVARC', 'ARDVARC');

/* Step 14 */
insert into stuff values ('kommodo',        'CANAD', 'CANAD');

/* Step 15 */
insert into stuff values ('aquifer',        'AGAFAR', 'AGAFAR');

/* Step 16 */
insert into stuff values ('sheldon',        'SHALDAN', 'SALDAN');

/* Step 17 */
insert into stuff values ('schmale',        'SSNAL', 'SNAL');
insert into stuff values ('pastisch',       'PASTAS','PASTAS');

/* Step 18 */
insert into stuff values ('byw',  'BYW', 'BY');

/* Step 19 */
insert into stuff values ('yankee',  'YANCY', 'YANCY');
insert into stuff values ('wary',  'WARY', 'WARY');
insert into stuff values ('sayz',  'SY', 'SY');

/* Step 20 problem */
insert into stuff values ('wrestle',  'WRASTL', 'RASTL');
insert into stuff values ('awray',  'AWRY', 'ARY');

/* Step 21 */
insert into stuff values ('zebra',  'ZABR', 'ZABR');
insert into stuff values ('xyz',  'XY', 'XY');

/* Step 22 */
insert into stuff values ('naysay',  'NAYSY', 'NASY');

/* Step 23 */
insert into stuff values ('hooroo',  'HARA', 'HAR');
insert into stuff values ('hoohah',  'HAHAH', 'H');

/* Step 24 */
insert into stuff values ('wolloomoolloo',  'WALANALA', 'WALANAL');

/* Step 25 */
insert into stuff values ('awful',          'AWFAL', 'AFAL');
insert into stuff values ('european',       'EARAPAN', 'ERAPAN');
insert into stuff values ('india',          'INDA', 'IND');
insert into stuff values ('omnipotent',     'ONAPATAD', 'ONAPATAD');
insert into stuff values ('uncle',          'UNCL', 'UNCL');
insert into stuff values ('Wollongong',     'WALANGANG', 'WALANGANG');
insert into stuff values ('Woollen gong',   'WALANGANG', 'WALANGANG');

\p\g
select string, expected_nysiis, nysiis(string) from stuff
where nysiis(string) != expected_nysiis;
\p\g
select string, expected_nysiis_o, nysiis_o(string) from stuff
where nysiis_o(string) != expected_nysiis_o;
\p\g
\q
SQL_END
